﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_WEIGHT_TRAINING_Get_Search_Count')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_WEIGHT_TRAINING_Get_Search_Count
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_WEIGHT_TRAINING_Get_Search_Count
**	Desc : 운동종류관리의 검색결과수를 카운트한다.
**	Test Exec Query : Exec usp_UPDMS_FIT_WEIGHT_TRAINING_Get_Search_Count
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING.cs
**	Program ID : Fit1011m
**	Auth : 송시명
**	Date : 2014-05-29
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_WEIGHT_TRAINING_Get_Search_Count]
@ls_training_dt_from nvarchar(10),
@ls_training_dt_to nvarchar(10),
@ls_training_part nvarchar(max),
@ls_training_type_id nvarchar(max),
@ls_location nvarchar(100),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @training_parts nvarchar(max)
IF @ls_training_part = ''
BEGIN
    SET @training_parts = '%'
END
ELSE
BEGIN
    SET @training_parts = @ls_training_part
END

DECLARE @training_type_ids nvarchar(max)
IF @ls_training_type_id = ''
BEGIN
    SET @training_type_ids = '%'
END
ELSE
BEGIN
    SET @training_type_ids = @ls_training_type_id
END

SELECT COUNT(z.Seq) AS Search_Count
  FROM (
       SELECT DISTINCT
              A.Seq,
              A.Training_Id,
              A.Training_Dt,
              A.Start_Hour,
              A.Start_Minute,
              A.End_Hour,
              A.End_Minute,
              A.Location
         FROM UPDMS_FIT_WEIGHT_TRAINING A WITH(NOLOCK)
	     JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
	     JOIN UPDMS_FIT_TRAINING_TYPE C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
         JOIN UPDMS_MST_DATE D WITH(NOLOCK) ON A.Training_Dt = D.Dt
        WHERE A.Training_Dt >= @ls_training_dt_from
          AND A.Training_Dt <= @ls_training_dt_to
          AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))
              OR C.Training_Part LIKE @training_parts
              )
          AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))
              OR C.Training_Type_Id LIKE @training_type_ids
              )
          AND A.Location LIKE '%' + @ls_location + '%'
       ) z

GO